#!/bin/bash

if [ -n "$1" ] ; then
  dada_date=$1
else
  dada_date=`date -d "-1 days" +%F`
fi

ADS_TRAFFIC_PAGE_VIEW_REPORT_SQL="
INSERT OVERWRITE TABLE jtp_app_ads.ads_traffic_page_view_report
SELECT '${dada_date}' AS  dt,
       1                                                         AS recent_days,
       province,
       city,
       sum(session_pv)                                             AS page_view_count,
       count(DISTINCT device_id)                                   AS user_count,
       count(session_id)                                           AS session_count,
       round(count(session_id) / count(DISTINCT device_id), 2)     AS avg_user_session_count,
       round(sum(session_during_time), 2)                          AS session_during_time,
       round(avg(session_during_time), 2)                          AS avg_session_during_time,
       sum(if(session_pv = 1, 1, 0))                               AS bounce_count,
       round(sum(if(session_pv = 1, 1, 0)) / count(session_id), 4) AS bounce_count_rate
FROM jtp_app_dws.dws_event_log_session_agg
WHERE province IS NOT NULL AND city IS NOT NULL
GROUP BY province, city
UNION ALL
SELECT dt,
       recent_days,
       province,
       city,
       page_view_count,
       user_count,
       session_count,
       avg_user_session_count,
       session_during_time,
       avg_session_during_time,
       bounce_count,
       bounce_count_rate FROM jtp_app_ads.ads_traffic_page_view_report
       WHERE province IS NOT NULL AND city IS NOT NULL
;"

/opt/module/spark/bin/beeline -u jdbc:hive2://node101:10001  -n bwie -e "${ADS_TRAFFIC_PAGE_VIEW_REPORT_SQL}"
